﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Data;
using BBS.Entity;

namespace BBS.Data.Forum
{
    public class AccountTable
    {
        private static string _connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        private BaseTable _table = new BaseTable(_connstr, "Account");
        /// <summary>
        /// return account table join account_info table record
        /// </summary>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <returns>user record</returns>
        public DataTable GetUserByEmailAndPassword(string email, string password)
        {
            string sql = "select * from [account] inner join [account_info] on [account].id=[account_info].account_id where account.email = @email and account.password = @password";
            return _table.ExecuteDataTable(sql, new OleDbParameter("@email", email), new OleDbParameter("@password", password));
        }
        /// <summary>
        /// check email and nick name
        /// </summary>
        /// <param name="email"></param>
        /// <param name="nick"></param>
        /// <returns></returns>
        public int CheckEmailAndNickName(string email, string nick)
        {
            string sql = "select count(1) from account where 1=1 ";
            var parameters = new List<OleDbParameter>();
            if (!string.IsNullOrEmpty(email))
            {
                sql += " and email = @email";
                parameters.Add(new OleDbParameter("@email", email));
            }
            if (!string.IsNullOrEmpty(nick))
            {
                sql += " and nick_name = @nick";
                parameters.Add(new OleDbParameter("@nick", nick));
            }
            string s = _table.ExecuteDataTable(sql, parameters.ToArray()).Rows[0][0].ToString();
            return int.Parse(s);
        }

        public Account CreateAccount(Account account)
        {
            using (OleDbConnection conn = new OleDbConnection(_connstr))
            {
                conn.Open();
                var transaction = conn.BeginTransaction();
                // "insert into [account] (email,[password],nick_name,birthday,gender,qq,msn,sign,register_time,type) values (@email,@password,@nick_name,@birthday,@gender,@qq,@msn,@sign,@register_time,@type)"
                Access.ExecuteNonQuery(transaction, CommandType.Text,
                                      "insert into [account] (email,[password],nick_name,birthday,gender,qq,msn,sign,register_time,type) values (@email,@password,@nick_name,@birthday,@gender,@qq,@msn,@sign,@register_time,@type)",
                                       new OleDbParameter("email", account.Email),
                                       new OleDbParameter("password", account.Password),
                                       new OleDbParameter("nick_name", account.NickName),
                                       new OleDbParameter("birthday", account.Birthday.Date),
                                       new OleDbParameter("gender", account.Gender),
                                       new OleDbParameter("qq", account.QQ),
                                       new OleDbParameter("msn", account.MSN),
                                       new OleDbParameter("sign", account.Sign),
                                       new OleDbParameter("register_time", account.RegisterTime.Date),
                                       new OleDbParameter("type", account.Type)
                    );
                object id = Access.ExecuteScalar(transaction, CommandType.Text, "SELECT @@IDENTITY as id");
                account.Id = int.Parse(id.ToString());
                Access.ExecuteNonQuery(transaction, CommandType.Text,
                                       "insert into [account_info] (account_id,points,last_time,last_ip) values (@account_id,@points,@last_time,@last_ip)",
                                       new OleDbParameter("account_id", account.Id),
                                       new OleDbParameter("points", account.Points),
                                       new OleDbParameter("last_time", account.LastTime.Date),
                                       new OleDbParameter("last_ip", account.LastIp)
                    );
                transaction.Commit();
            }
            return account;
        }
    }
}
